﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WeberTimeTracker.Models;

namespace WeberTimeTracker.Controllers
{
	public class InvoiceController : Controller
	{
		// GET: Index/
		public ActionResult Index()
		{
			using (TimeTrackerContext data = new TimeTrackerContext())
			{
				List<ClientProjects> clientView = new List<ClientProjects>();
				var result = data.Database.SqlQuery<ClientProjects>("[dbo].[client_projects]");
				foreach (ClientProjects proj in result)
				{
					var name = from n in data.Clients where n.Client_ID == proj.Client_ID select n.Name;
					foreach (string s in name)
					{
						proj.Client_Name = s;
						clientView.Add(proj);
					}
				}
				return View(clientView);
			}
		}

		// GET: /Invoice/
		public ActionResult InvoiceView(int id)
		{
			using (TimeTrackerContext data = new TimeTrackerContext())
			{
				List<InvoicedHours> invoice = new List<InvoicedHours>();
				var result = data.Database.SqlQuery<InvoicedHours>("[dbo].[invoiced_hours] @p0", id);
				foreach (InvoicedHours proj in result)
				{
					proj.Client_ID = id;
					var name = from n in data.Clients where n.Client_ID == proj.Client_ID select n.Name;
					foreach (string s in name)
					{
						proj.Client_Name = s;
						invoice.Add(proj);
					}
				}
				return View(invoice);
			}
		}

	  // GET: /Invoice/Edit
		public ActionResult Edit(int clientid, string projectname)
		{
			using (TimeTrackerContext data = new TimeTrackerContext())
			{
				var projectid = from n in data.Projects where n.Project_Name == projectname select n.Project_ID;

				foreach (int i in projectid)
				{
					//update times table to invoiced = true
					var results = data.Database.SqlQuery<InvoicedHours>("[dbo].[markasinvoiced]", clientid, i);

					var TransactionToUpdate = (from t in data.Times 
																		 join p in data.Projects on t.Project_ID equals p.Project_ID 
																		 join c in data.Clients on p.Client_ID equals c.Client_ID 
																		 where t.Project_ID == i && clientid == c.Client_ID && t.Invoiced == false select t).ToList();

					foreach (Time d in TransactionToUpdate)
					{
					  d.Invoiced = true;
					}
				}

				data.SaveChanges();
				
				List<InvoicedHours> invoice = new List<InvoicedHours>();
				var result = data.Database.SqlQuery<InvoicedHours>("[dbo].[previously_invoiced]");
				foreach (InvoicedHours proj in result)
				{
					var client = from n in data.Projects where n.Project_Name == proj.Project_Name select n.Client_ID;
					foreach (int i in client)
					{
						proj.Client_ID = i;
					}

					var name = from n in data.Clients where n.Client_ID == proj.Client_ID select n.Name;
					foreach (string s in name)
					{
						proj.Client_Name = s;
						invoice.Add(proj);
					}
				}
				return View(invoice);
			}
		}
	}
}